<?php

/* * ************************************************************************* 
 * sqlrelay.class.php
 * Encoding : UTF-8
 * Desc. :数据库实现类
 * Created on : 2012-11-19 17:54:05 by Aiden , aiden.ke@doosnet.net
 * Updated on : 2012-11-19 17:54:05 by Aiden , aiden.ke@doosnet.net
 * ************************************************************************* */
defined('IN_SPIDER') or exit('No permission resources.');

final class sqlrelay {

    /**
     * 数据库配置信息
     */
    private $config = null;

    /**
     * 数据库连接资源句柄
     */
    public $link = null;

    /**
     * 最近一次查询资源句柄
     */
    public $lastqueryid = null;

    /**
     * 受影响结果数
     */
    public static $_affected_rows = null;

    /**
     *  统计数据库查询次数
     */
    public $querycount = 0;

    public function __construct() {
        
    }

    /**
     * 打开数据库连接,有可能不真实连接数据库
     * @param $config    数据库连接参数
     *             
     * @return void
     */
    public function open($config) {
        $this->config = $config;
        if ($config['autoconnect'] == 1) {
            $this->connect();
        }
    }

    /**
     * 真正开启数据库连接
     *             
     * @return void
     */
    public function connect() {
        echo '连接时间：' . getmicrotime(TRUE) . '<br/>';
        if (!$this->link = sqlrcon_alloc($this->config['hostname'], $this->config['port'], $this->config['socket'], $this->config['username'], $this->config['password'], $this->config['retrytime'], $this->config['trynum'])) {
            $this->halt('Can not connect to MySQL server');
            return false;
        };
        $this->setUtf8AndGbk();
        return $this->link;
    }

    public function setUtf8AndGbk() {
        if (is_resource($this->link)) {
            $this->lastqueryid = sqlrcur_alloc($this->link);
        }
        sqlrcur_sendQuery($this->lastqueryid, " SET NAMES GBK ");
        sqlrcur_sendQuery($this->lastqueryid, " set names utf8 ");
    }

    /**
     * 数据库查询执行方法
     * @param $sql 要执行的sql语句
     * @return 查询资源句柄
     */
    private function execute($sql) {
        if (!is_resource($this->link)) {
            $this->connect();
        }
        if ($this->lastqueryid) {
            $this->free();
        }
        if (is_resource($this->link)) {
            $this->lastqueryid = sqlrcur_alloc($this->link);
        }
        if (!$this->lastqueryid) {
            return false;
        }
        $this->querycount++;

        if (!$this->lastqueryid) {
            return false;
        }

        if (pc_base::load_config('system', 'debug')) {
            echo '<br/>';
            echo $sql;
            echo '<hr/>';
        }
        
        $this->setUtf8AndGbk();
//        @sqlrcur_prepareQuery($this->lastqueryid, $sql);
//        $retval = @sqlrcur_executeQuery($this->lastqueryid);
        $retval = @sqlrcur_sendQuery($this->lastqueryid, $sql);

        self::$_affected_rows = sqlrcur_affectedRows($this->lastqueryid);
        sqlrcon_endSession($this->link);
        if ($retval === false) {
            return false;
        }
        return $this->lastqueryid;
    }

    /**
     * 执行sql查询
     * @param $data          需要查询的字段值[例`name`,`gender`,`birthday`]
     * @param $table         数据表
     * @param $where         查询条件[例`name`='$name']
     * @param $limit         返回结果范围[例：10或10,10 默认为空]
     * @param $order         排序方式    [默认按数据库默认方式排序]
     * @param $group         分组方式    [默认为空]
     * @param $key           返回数组按键名排序
     * @return array         查询结果集数组
     */
    public function select($data, $table, $where = '', $limit = '', $order = '', $group = '', $key = '') {
        $where = $where == '' ? '' : ' WHERE ' . utog($where);
        $order = $order == '' ? '' : ' ORDER BY ' . $order;
        $group = $group == '' ? '' : ' GROUP BY ' . $group;
        $limit = $limit == '' ? '' : ' LIMIT ' . $limit;
        $field = explode(',', $data);
        array_walk($field, array($this, 'add_special_char'));
        $data = implode(',', $field);

        $sql = 'SELECT ' . $data . ' FROM ' . $table . $where . $group . $order . $limit;

        $this->execute($sql);
        if (!is_resource($this->lastqueryid)) {
            return $this->lastqueryid;
        }

        $datalist = array();
        $rownum = 0;
        while (($rs = $this->fetch_next($rownum)) != false) {
            if ($key) {
                $datalist[$rs[$key]] = $rs;
            } else {
                $datalist[] = $rs;
            }
            $rownum++;
        }
        $this->free_result();
        return array_iconv($datalist);
    }

    /**
     * 获取单条记录查询
     * @param $data          需要查询的字段值[例`name`,`gender`,`birthday`]
     * @param $table         数据表
     * @param $where         查询条件
     * @param $order         排序方式    [默认按数据库默认方式排序]
     * @param $group         分组方式    [默认为空]
     * @return array/null    数据查询结果集,如果不存在，则返回空
     */
    public function get_one($data, $table, $where = '', $order = '', $group = '') {
        $where = $where == '' ? '' : ' WHERE ' . $where;
        $order = $order == '' ? '' : ' ORDER BY ' . $order;
        $group = $group == '' ? '' : ' GROUP BY ' . $group;
        $limit = ' LIMIT 1';
        $field = explode(',', $data);
        array_walk($field, array($this, 'add_special_char'));
        $data = implode(',', $field);

        $sql = 'SELECT ' . $data . ' FROM ' . $table . $where . $group . $order . $limit;
        $this->execute($sql);
        $res = $this->fetch_next();
        $this->free_result();
        $res = array_iconv($res);
        return $res;
    }

    /**
     * 遍历查询结果集
     * @param rownum
     * @return array
     */
    public function fetch_next($rownum = 0) {
        $res = sqlrcur_getRowAssoc($this->lastqueryid, $rownum);
        if (!$res) {
            $this->free_result();
        }
        return $res;
    }

    /**
     * 释放查询资源
     * @return void
     */
    public function free_result() {
        if (is_resource($this->lastqueryid)) {
            sqlrcur_free($this->lastqueryid);
            $this->lastqueryid = null;
        }
    }

    /**
     * 直接执行sql查询
     * @param $sql                            查询sql语句
     * @return    boolean/query resource      如果为查询语句，返回资源句柄，否则返回true/false
     */
    public function query($sql) {
        $this->execute($sql);
        if (!is_resource($this->lastqueryid)) {
            return $this->lastqueryid;
        }

        $datalist = array();
        $rownum = 0;
        while (($rs = $this->fetch_next($rownum)) != false) {
            $datalist[] = $rs;
            $rownum++;
        }
        $this->free_result();
        return $datalist;
    }

    /**
     * 执行添加记录操作
     * @param $data         要增加的数据，参数为数组。数组key为字段值，数组值为数据取值
     * @param $table        数据表
     * @return boolean
     */
    public function insert($data, $table, $return_insert_id = false, $relpace = false) {
        if (!is_array($data) || $table == '' || count($data) == 0) {
            return false;
        }
        $fielddata = array_keys($data);
        $valuedata = array_values($data);
        array_walk($fielddata, array($this, 'add_special_char'));
        array_walk($valuedata, array($this, 'escape_string'));

        $field = implode(',', $fielddata);
        $value = implode(',', $valuedata);

        $cmd = 'INSERT INTO';
        $sql = $cmd . ' ' . $table . '(' . $field . ') VALUES (' . $value . ')';
        $return = $this->execute($sql);
        return $return_insert_id ? $this->insert_id($table) : $return;
    }

    /**
     * 执行添加不重复记录操作
     * @param $data         要增加的数据，参数为数组。数组key为字段值，数组值为数据取值
     * @param $table        数据表
     * @param $where        不重复条件
     * @return boolean
     */
    public function insert_dual($data, $table, $where = '') {
        
        if (!is_array($data) || $table == '' || count($data) == 0 || $where == '') {
            return false;
        }
        $where = ' WHERE ' . $where;
        $fielddata = array_keys($data);
        $valuedata = array_values($data);
        array_walk($fielddata, array($this, 'add_special_char'));
        array_walk($valuedata, array($this, 'escape_string'));

        $field = implode(',', $fielddata);
        $value = implode(',', $valuedata);

        $cmd = 'INSERT INTO';
        $sql = $cmd . ' ' . $table . '(' . $field . ') SELECT ' . $value . 'FROM dual WHERE NOT EXISTS ( SELECT * FROM ' . $table . $where . ');';

        $return = $this->execute($sql);
        return $return;
    }

    /**
     * 获取最后一次添加记录的主键号
     * @return int 
     */
    public function insert_id($table) {
        return 0;
    }

    /**
     * 执行更新记录操作
     * @param $data         要更新的数据内容，参数可以为数组也可以为字符串，建议数组。
     *                       为数组时数组key为字段值，数组值为数据取值
     *                       为字符串时[例：`name`='phpcms',`hits`=`hits`+1]。
     *                       为数组时[例: array('name'=>'phpcms','password'=>'123456')]
     *                       数组可使用array('name'=>'+=1', 'base'=>'-=1');程序会自动解析为`name` = `name` + 1, `base` = `base` - 1
     * @param $table        数据表
     * @param $where        更新数据时的条件
     * @return boolean
     */
    public function update($data, $table, $where = '') {
        if ($table == '' or $where == '') {
            return false;
        }

        $where = ' WHERE ' . $where;
        $field = '';
        if (is_string($data) && $data != '') {
            $field = $data;
        } elseif (is_array($data) && count($data) > 0) {
            $fields = array();
            foreach ($data as $k => $v) {
                switch (substr($v, 0, 2)) {
                    case '+=':
                        $v = substr($v, 2);
                        if (is_numeric($v)) {
                            $fields[] = $this->add_special_char($k) . '=' . $this->add_special_char($k) . '+' . $this->escape_string($v, '', false);
                        } else {
                            continue;
                        }

                        break;
                    case '-=':
                        $v = substr($v, 2);
                        if (is_numeric($v)) {
                            $fields[] = $this->add_special_char($k) . '=' . $this->add_special_char($k) . '-' . $this->escape_string($v, '', false);
                        } else {
                            continue;
                        }
                        break;
                    default:
                        $fields[] = $this->add_special_char($k) . '=' . $this->escape_string($v);
                }
            }
            $field = implode(',', $fields);
        } else {
            return false;
        }
        $sql = 'UPDATE ' . $table . ' SET ' . $field . $where;
        return $this->execute($sql);

    }

    /**
     * 执行删除记录操作
     * @param $table         数据表
     * @param $where         删除数据条件,不充许为空。
     *                         如果要清空表，使用empty方法
     * @return boolean
     */
    public function delete($table, $where) {
        if ($table == '' || $where == '') {
            return false;
        }
        $where = ' WHERE ' . $where;
        $sql = 'DELETE FROM ' . $table . $where;
        return $this->execute($sql);
    }

    /**
     * 获取最后数据库操作影响到的条数
     * @return int
     */
    public function affected_rows() {
        if (self::$_affected_rows) {
            return self::$_affected_rows;
        } else {
            return 0;
        }
    }

    /**
     * 获取数据表主键
     * @param $table         数据表
     * @return array
     */
    public function get_primary($table) {
        $sql = "SHOW COLUMNS FROM $table";
        $this->execute($sql);
        $result = array();
        $rownum = 0;
        while ($r = $this->fetch_next($rownum)) {
            if ($r['Key'] == 'PRI')
                break;
            $rownum++;
        }
        return $r['Field'];
    }

    /**
     * 获取表字段
     * @param $table         数据表
     * @return array
     */
    public function get_fields($table) {
        $sql = "SHOW COLUMNS FROM $table";
        $fields = array();
        $this->execute($sql);
        $rownum = 0;
        while ($r = $this->fetch_next($rownum)) {
            $fields[$r['Field']] = $r['Type'];
            $rownum++;
        }
        return $fields;
    }

    /**
     * 检查不存在的字段
     * @param $table 表名
     * @return array
     */
    public function check_fields($table, $array) {
        $fields = $this->get_fields($table);
        $nofields = array();
        foreach ($array as $v) {
            if (!array_key_exists($v, $fields)) {
                $nofields[] = $v;
            }
        }
        return $nofields;
    }

    /**
     * 检查表是否存在
     * @param $table 表名
     * @return boolean
     */
    public function table_exists($table) {
        $tables = $this->list_tables();
        return in_array($table, $tables) ? 1 : 0;
    }

    public function list_tables() {
        $tables = array();
        $sql = "SHOW TABLES";
        $this->execute($sql);
        $rownum = 0;
        while ($r = $this->fetch_next($rownum)) {
            $tables[] = $r['Tables_in_' . $this->config['database']];
            $rownum++;
        }
        return $tables;
    }

    /**
     * 检查字段是否存在
     * @param $table 表名
     * @return boolean
     */
    public function field_exists($table, $field) {
        $fields = $this->get_fields($table);
        return array_key_exists($field, $fields);
    }

    /**
     * 返回结果的行数
     * @param unknown_type $sql
     */
    public function num_rows($sql) {
        $this->lastqueryid = $this->execute($sql);
        return sqlrcur_rowCount($this->lastqueryid);
    }

    /**
     * 返回结果的列数
     * @param unknown_type $sql
     */
    public function num_fields($sql) {
        $this->lastqueryid = $this->execute($sql);
        return sqlrcur_colCount($this->lastqueryid);
    }

    /**
     * 返回一行中的某列
     * @param unknown_type $sql
     * @param unknown_type $row
     */
    public function result($sql, $row) {
        $this->lastqueryid = $this->execute($sql);
        return @sqlrcur_getRowAssoc($this->lastqueryid, $row);
    }

    /**
     * 错误信息
     */
    public function error() {
        $error = @sqlrcur_errorMessage($this->link);
        return $error['message'];
    }

    /**
     * 错误编码 不可用
     */
    public function errno() {
        //$error = @oci_error($this->link);
        $error['code'] = '';
        return $error['code'];
    }

    /**
     * 版本信息
     */
    public function version() {
        if (!is_resource($this->link)) {
            $this->connect();
        }
        return sqlrcon_dbVersion($this->link);
    }

    /**
     * 关闭连接
     */
    public function close() {
        if (is_resource($this->link)) {
            @sqlrcon_free($this->link);
        }
    }

    /**
     * 展示报错信息
     * @param unknown_type $message
     * @param unknown_type $sql
     */
    public function halt($message = '', $sql = '') {
        if ($this->config['debug']) {
            if (is_array($message)) {
                $message = $message['message'];
            }
            $this->errormsg = "<b>MySQL Query : </b> $sql <br /><b> MySQL Error : </b>" . $this->error() . " <br /> <b>MySQL Errno : </b>" . $this->errno() . " <br /><b> Message : </b> $message <br />";
            $msg = $this->errormsg;
            echo '<div style="font-size:12px;text-align:left; border:1px solid #9cc9e0; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif;"><span>' . $msg . '</span></div>';
            exit;
        } else {
            return false;
        }
    }

    /**
     * 对字段两边加反引号，以保证数据库安全
     * @param $value 数组值
     */
    public function add_special_char(&$value) {
        if ('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
            //不处理包含* 或者 使用了sql方法。
        } else {
            $value = '`' . trim($value) . '`';
        }
        return $value;
    }

    /**
     * 对字段值两边加引号，以保证数据库安全
     * @param $value 数组值
     * @param $key 数组key
     * @param $quotation 
     */
    public function escape_string(&$value, $key='', $quotation = 1) {
        if ($quotation) {
            $q = '\'';
        } else {
            $q = '';
        }
        $value = $q . $value . $q;
        return $value;
    }

    /**
     * 释放查询结果
     */
    public function free() {
        sqlrcur_free($this->lastqueryid);
        $this->lastqueryid = null;
    }

}

?>
